# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql


class LianjiaPipeline:

    def __init__(self) -> None:
        self.conn = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                                    password="123456", database="lianjia", charset="utf8")
        self.cur = self.conn.cursor()
        try:
            self.cur.execute('''
                create table home(
                    type varchar(100), 
                    room_name varchar(100), 
                    room_type varchar(100), 
                    room_dir varchar(100), 
                    room_addr_qu varchar(100), 
                    room_addr_zhan varchar(100), 
                    room_addr_xq varchar(100), 
                    room_area varchar(100), 
                    room_price varchar(100), 
                    room_floor varchar(100),
                    room_broker varchar(100), 
                    broker_no varchar(100) 
                );'''
                             )
        except:
            self.cur.execute("delete from home;")

    def process_item(self, item, spider):
        insert_sql = f'''insert into home values('{item["type"]}',
                                                '{item["room_name"]}',
                                                '{item["room_type"]}',
                                                '{item["room_dir"]}',
                                                '{item["room_addr_qu"]}',
                                                '{item["room_addr_zhan"]}',
                                                '{item["room_addr_xq"]}',
                                                '{item["room_area"]}',
                                                '{item["room_price"]}',
                                                '{item["room_floor"]}',
                                                '{item["room_broker"]}',
                                                '{item["broker_no"]}')'''
        self.cur.execute(insert_sql)
        self.conn.commit()
        return item

    def __del__(self):
        self.cur.close()
        self.conn.commit()
        self.conn.close()
